

/**
	Author	    : Rohan
	Date	    : June 30th 2004
	Description : This procedure is used to insert or update the epicore customer
		      details in the Chiptrac database. This procedure is called from 
		      a trigger on the customer table in the Epicore database
*/

alter proc sp_save_epicore_customer
(
@cust_number     varchar(50),
@cust_name       varchar(50),
@sales_person    varchar(50),
@ship_method     varchar(50),
@contact_person  varchar(50),
@address1        varchar(50),
@address2        varchar(50),
@address3        varchar(50),
@address4        varchar(50),
@city            varchar(50),
@state           varchar(50),
@country         varchar(50),
@zip             varchar(50),
@phone           varchar(50),
@fax             varchar(50),
@ship_to_code    varchar(8),
@region          varchar(50),
@cust_id         int OUT
)
AS
BEGIN


declare @bill_addr_id int
declare @ship_addr_id int

--saving the billing address details
exec sp_save_epicore_address @cust_number, @address1, @address2, @address3, @address4, @city, @state, @country, @zip, @phone, @fax, @contact_person, '', @bill_addr_id OUT

--checking if the customer is existing
select @cust_id = Customer_Id from Customer where C_Number = @cust_number
if( @@rowcount = 0 )
begin
	--setting the shipping address same as the billing address ID for the first time
	set @ship_addr_id = @bill_addr_id

	--insert the customer details
	insert into customer( C_Name, C_Number, C_Contact_Person, C_Shipping_Method_1, C_Shipping_Method_2, C_Shipping_method_3, C_Act_Number_1, C_Act_Number_2, C_Act_Number_3, C_Region, C_Rep_Office, C_Sales_Person, C_Shipping_Instructions, C_Default_Shipping_Address_ID, C_Default_Billing_Address_ID, C_Priority, C_Cancellation_Window, C_Revision_Restriction, C_Location_Restriction, C_Datecode_Restriction, C_Traycode_Restriction, C_Least_Shippable_Qty, C_Credit_Limit, C_Representative_ID, C_Representative_Name, C_Net_Terms, C_Shipping_Restriction, C_NoOf_ShippingDays_NotEarlier_Than, C_Disabled )
		    values  ( @cust_name, @cust_number, '', @ship_method, '', '', '', '', '', @region, '', @sales_person, '', @ship_addr_id, @bill_addr_id, '', 0, 1, 1, 1, 1, 0, 0, '', '', '', 1, 0, 1 )

	select @cust_id = @@identity

end
else
begin
	--Checking if any default shipping address is specified
	if( @ship_to_code = '' )
	begin
		--If no default shipping address is given then shipping address is taken as the billing address
		set @ship_addr_id = @bill_addr_id	
	end
	else
	begin
		--retreiving the shipping address ID
		select @ship_addr_id = Address_ID from Address  (NOLOCK) where epicore_ship_code = @ship_to_code and ( exists( select 1 from Customer_Shipping_Address_Table (NOLOCK) where customer_id = @cust_id and address_id = address.address_id ) or exists ( select 1 from customer (NOLOCK) where customer_id = @cust_id and ( C_Default_Shipping_Address_ID = address.address_id or C_Default_Billing_Address_ID = address.address_id  ) ) ) 
		if( @@rowcount = 0 )
		begin
			--if the shipping address is not present then set the shipping address as the billing address
			set @ship_addr_id = @bill_addr_id	
		end
	end

	--update the customer details
	update customer set C_Name                        = @cust_name, 
		            C_Number                      = @cust_number, 
		            C_Shipping_Method_1           = @ship_method, 
		            C_Sales_Person                = @sales_person, 
			    C_Region                      = @region,
		            C_Default_Shipping_Address_ID = @ship_addr_id, 
		            C_Default_Billing_Address_ID  = @bill_addr_id where Customer_Id = @cust_id
end

--inserted the shipping method and sales person in there corresponding master tables
exec sp_ct_sls_insert_customer_related_if_not_existing @cust_id, @ship_method, '', @sales_person

--Checking if the region is existing in the master table
if( ( @region is not null ) and ( @region != '' ) )
begin
	select @region = Region_Name from Regions where Region_Name = @region and Customer_Id = @cust_id
	if( @@rowcount = 0 )
	begin
		insert into Regions ( Region_Name, Deactivated, Customer_Id ) values ( @region, 0, @cust_id )
	end
end

--delete if the shipping address mapping is existing in the Customer_Shipping_Address_Table
delete from Customer_Shipping_Address_Table where Customer_ID = @cust_id and Address_Id = @ship_addr_id


END